using SPI.AgentServer.Services.Execution;
using System.Text;
using System.Globalization;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 查询在校外来车辆工具 - 基于停车订单表（按月分表）
/// 查询当前在校的访客车辆（未出场且非包月用户）
/// </summary>
public class GetOnCampusVisitorVehiclesTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ILogger<GetOnCampusVisitorVehiclesTool> _logger;

    public string Name => "get_oncampus_visitor_vehicles";
    public string Description => "查询当前在校的外来访客车辆信息（基于停车订单表），包括车牌号、入场时间、停留时长、车辆图片等详细信息。支持指定月份查询，默认查询当前月份。";

    public GetOnCampusVisitorVehiclesTool(
        IDatabaseExecutor executor,
        ILogger<GetOnCampusVisitorVehiclesTool> logger)
    {
        _executor = executor;
        _logger = logger;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (!ValidateParameters(parameters, out var errors))
        {
            return new ToolResult
            {
                Success = false,
                Errors = errors
            };
        }

        try
        {
            // 获取查询月份参数，默认使用当前月份
            DateTime queryMonth = DateTime.Now;
            if (parameters.ContainsKey("month") && parameters["month"] != null)
            {
                var monthStr = parameters["month"].ToString();
                if (!string.IsNullOrEmpty(monthStr))
                {
                    if (DateTime.TryParse(monthStr, out var parsedMonth))
                    {
                        queryMonth = parsedMonth;
                    }
                    else if (DateTime.TryParseExact(monthStr, "yyyy-MM", CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedMonth))
                    {
                        queryMonth = parsedMonth;
                    }
                }
            }

            // 生成表名后缀（格式：YYYYMM01）
            string tableSuffix = queryMonth.ToString("yyyyMM01");
            
            // 计算查询的时间范围
            var startTime = new DateTime(queryMonth.Year, queryMonth.Month, 1, 0, 0, 0);
            var endTime = startTime.AddMonths(1).AddSeconds(-1); // 月末最后一秒
            
            string startTimeStr = startTime.ToString("yyyy-MM-dd HH:mm:ss");
            string endTimeStr = endTime.ToString("yyyy-MM-dd HH:mm:ss");

            // 构建SQL查询
            var sql = BuildQuery(tableSuffix, startTimeStr, endTimeStr);

            _logger.LogInformation("Executing on-campus visitor vehicles query for month {Month} with SQL: {Sql}", 
                queryMonth.ToString("yyyy-MM"), sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = $"在校外来车辆查询成功（{queryMonth:yyyy年MM月}）",
                Metadata = new Dictionary<string, object>
                {
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql,
                    ["queryMonth"] = queryMonth.ToString("yyyy-MM"),
                    ["tableSuffix"] = tableSuffix
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing on-campus visitor vehicles query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询错误: {ex.Message}" }
            };
        }
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        
        // month 参数是可选的，如果提供则验证格式
        if (parameters.ContainsKey("month") && parameters["month"] != null)
        {
            var monthStr = parameters["month"].ToString();
            if (!string.IsNullOrEmpty(monthStr))
            {
                // 尝试解析日期
                if (!DateTime.TryParse(monthStr, out _) && 
                    !DateTime.TryParseExact(monthStr, "yyyy-MM", CultureInfo.InvariantCulture, DateTimeStyles.None, out _))
                {
                    errors.Add("月份格式不正确，请使用 'yyyy-MM' 或完整日期格式");
                    return false;
                }
            }
        }
        
        return true;
    }

    /// <summary>
    /// 构建查询在校外来车辆的SQL
    /// </summary>
    private string BuildQuery(string tableSuffix, string startTime, string endTime)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    t1.CarNum AS '车牌号',");
        sb.AppendLine("    t1.CarNumColor AS '车牌颜色',");
        sb.AppendLine("    DATE_FORMAT(t1.InDt, '%Y-%m-%d %H:%i:%s') AS '进入时间',");
        sb.AppendLine("    t1.InEntranceName AS '入口名称',");
        sb.AppendLine("    TIMESTAMPDIFF(MINUTE, t1.InDt, NOW()) AS '停留分钟数',");
        sb.AppendLine("    CONCAT(");
        sb.AppendLine("        FLOOR(TIMESTAMPDIFF(MINUTE, t1.InDt, NOW()) / 1440), '天',");
        sb.AppendLine("        FLOOR(MOD(TIMESTAMPDIFF(MINUTE, t1.InDt, NOW()), 1440) / 60), '时',");
        sb.AppendLine("        MOD(TIMESTAMPDIFF(MINUTE, t1.InDt, NOW()), 60), '分'");
        sb.AppendLine("    ) AS '停留时长',");
        sb.AppendLine("    DATE_FORMAT(t1.CreateTime, '%Y-%m-%d %H:%i:%s') AS '记录创建时间'");
        sb.AppendLine($"FROM tblparkinginorder_{tableSuffix} t1");
        sb.AppendLine($"LEFT JOIN tblparkingoutorder_{tableSuffix} t2");
        sb.AppendLine("    ON t1.CarOrderId = t2.CarOrderId");
        sb.AppendLine("WHERE t2.CarOrderId IS NULL  -- 未出场");
        sb.AppendLine("  AND t1.CarNum IS NOT NULL  -- 有车牌");
        sb.AppendLine("  AND (t1.CarCusName IS NULL OR t1.CarCusName = '')  -- 访客（非包月用户）");
        sb.AppendLine($"  AND t1.InDt >= '{startTime}'  -- 开始时间");
        sb.AppendLine($"  AND t1.InDt <= '{endTime}'  -- 结束时间");
        sb.AppendLine("ORDER BY t1.InDt DESC");

        return sb.ToString();
    }
}

